<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
    <title id="or137116">pg_dumpall</title>
    <body>
        <p id="client_util_desc">Extracts all databases in a Greenplum Database system
            to a single script file or other archive file.</p>
        <section id="section2">
            <title>Synopsis</title>
            <codeblock id="client_util_synopsis">pg_dumpall [&lt;connection-option> ...] [&lt;dump_option> ...]

pg_dumpall -? | --help

pg_dumpall -V | --version</codeblock>
        </section>
        <section id="section3">
            <title>Description</title>
            <p><codeph>pg_dumpall</codeph> is a standard PostgreSQL utility for backing up all
                databases in a Greenplum Database (or PostgreSQL) instance, and is also supported in
                Greenplum Database. It creates a single (non-parallel) dump file. <ph>For routine
                    backups of Greenplum Database it is better to use the Greenplum Database backup
                    utility, <xref href="gpbackup.xml#topic1"/>, for the best performance.</ph></p>
            <p><codeph>pg_dumpall</codeph> creates a single script file that contains SQL commands
                that can be used as input to <xref href="psql.xml#topic1" type="topic" format="dita"
                /> to restore the databases. It does this by calling <xref href="pg_dump.xml#topic1"
                    type="topic" format="dita"/> for each database. <codeph>pg_dumpall</codeph> also
                dumps global objects that are common to all databases. (<codeph>pg_dump</codeph>
                does not save these objects.) This currently includes information about database
                users and groups, and access permissions that apply to databases as a whole.</p>
            <p>Since <codeph>pg_dumpall</codeph> reads tables from all databases you will most
                likely have to connect as a database superuser in order to produce a complete dump.
                Also you will need superuser privileges to run the saved script in order to be
                allowed to add users and groups, and to create databases.</p>
            <p>The SQL script will be written to the standard output. Use the
              <codeph>[-f | --file]</codeph> option or shell operators
                to redirect it into a file.</p>
            <p><codeph>pg_dumpall</codeph> needs to connect several times to the Greenplum Database master server (once per database). If you use password
                authentication it is likely to ask for a password each time. It is convenient to
                have a <codeph>~/.pgpass</codeph> file in such cases.</p>
        </section>
        <section id="section4">
            <title>Options</title>
            <sectiondiv id="section5">
                <b>Dump Options</b>
                <parml>
                    <plentry>
                        <pt>-a | --data-only</pt>
                        <pd>Dump only the data, not the schema (data definitions). This option is
                            only meaningful for the plain-text format. For the archive formats, you
                            may specify the option when you call <xref
                                href="./pg_restore.xml#topic1" type="topic" format="dita"/>. </pd>
                    </plentry>
                    <plentry>
                        <pt>-c | --clean</pt>
                        <pd>Output commands to clean (drop) database objects prior to (the commands
                            for) creating them. This option is only meaningful for the plain-text
                            format. For the archive formats, you may specify the option when you
                            call <xref href="./pg_restore.xml#topic1" type="topic" format="dita"/>. </pd>
                    </plentry>
                    <plentry>
                        <pt>-f <varname>filename</varname> | --file=<varname>filename</varname></pt>
                        <pd>Send output to the specified file. </pd>
                    </plentry>
                    <plentry>
                        <pt>-g | --globals-only</pt>
                        <pd>Dump only global objects (roles and tablespaces), no databases.</pd>
                    </plentry>
                    <plentry>
                        <pt>-o | --oids</pt>
                        <pd>Dump object identifiers (OIDs) as part of the data for every table. Use
                            of this option is not recommended for files that are intended to be
                            restored into Greenplum Database.</pd>
                    </plentry>
                    <plentry>
                        <pt>-O | --no-owner</pt>
                        <pd>Do not output commands to set ownership of objects to match the original
                            database. By default, <xref href="pg_dump.xml#topic1" type="topic"
                                format="dita"/> issues <codeph>ALTER OWNER</codeph> or <codeph>SET
                                SESSION AUTHORIZATION</codeph> statements to set ownership of
                            created database objects. These statements will fail when the script is
                            run unless it is started by a superuser (or the same user that owns all
                            of the objects in the script). To make a script that can be restored by
                            any user, but will give that user ownership of all the objects, specify
                                <codeph>-O</codeph>. This option is only meaningful for the
                            plain-text format. For the archive formats, you may specify the option
                            when you call <xref href="./pg_restore.xml#topic1" type="topic"
                                format="dita"/>.</pd>
                    </plentry>
                    <plentry>
                        <pt>-r | --roles-only</pt>
                        <pd>Dump only roles, not databases or tablespaces.</pd>
                    </plentry>
                    <plentry>
                        <pt>-s | --schema-only</pt>
                        <pd>Dump only the object definitions (schema), not data.</pd>
                    </plentry>
                    <plentry>
                        <pt>-S <varname>username</varname> |
                            --superuser=<varname>username</varname></pt>
                        <pd>Specify the superuser user name to use when disabling triggers. This is
                            relevant only if <codeph>--disable-triggers</codeph> is used. It is
                            better to leave this out, and instead start the resulting script as a
                                superuser.<note>Greenplum Database does not support user-defined
                                triggers.</note></pd>
                    </plentry>
                    <plentry>
                        <pt>-t | --tablespaces-only</pt>
                        <pd>Dump only tablespaces, not databases or roles.</pd>
                    </plentry>
                    <plentry>
                        <pt>-v | --verbose</pt>
                        <pd>Specifies verbose mode. This will cause <codeph><xref
                                    href="pg_dump.xml#topic1" type="topic" format="dita"/></codeph>
                            to output detailed object comments and start/stop times to the dump
                            file, and progress messages to standard error.</pd>
                    </plentry>
                    <plentry>
                      <pt>-V | --version</pt>
                      <pd>Print the <codeph>pg_dumpall</codeph> version and exit.</pd>
                    </plentry>
                    <plentry>
                        <pt>-x | --no-privileges | --no-acl</pt>
                        <pd>Prevent dumping of access privileges (<codeph>GRANT/REVOKE</codeph>
                            commands).</pd>
                    </plentry>
                    <plentry>
                        <pt>--binary-upgrade</pt>
                        <pd>This option is for use by in-place upgrade utilities. Its use for other
                            purposes is not recommended or supported. The behavior of the option may
                            change in future releases without notice.</pd>
                    </plentry>
                    <plentry>
                        <pt>--column-inserts | --attribute-inserts</pt>
                        <pd>Dump data as <codeph>INSERT</codeph> commands with explicit column names
                                (<codeph>INSERT INTO &lt;table> (&lt;column>, ...) VALUES
                                ...</codeph>). This will make restoration very slow; it is mainly
                            useful for making dumps that can be loaded into non-PostgreSQL-based
                            databases. Also, since this option generates a separate command for each
                            row, an error in reloading a row causes only that row to be lost rather
                            than the entire table contents.</pd>
                    </plentry>
                    <plentry>
                        <pt>--disable-dollar-quoting</pt>
                        <pd>This option disables the use of dollar quoting for function bodies, and
                            forces them to be quoted using SQL standard string syntax.</pd>
                    </plentry>
                    <plentry>
                        <pt>--disable-triggers</pt>
                        <pd>This option is relevant only when creating a data-only dump. It
                            instructs <codeph>pg_dumpall</codeph> to include commands to temporarily
                            disable triggers on the target tables while the data is reloaded. Use
                            this if you have triggers on the tables that you do not want to invoke
                            during data reload. The commands emitted for
                                <codeph>--disable-triggers</codeph> must be done as superuser. So,
                            you should also specify a superuser name with <codeph>-S</codeph>, or
                            preferably be careful to start the resulting script as a
                                superuser.<note>Greenplum Database does not support user-defined
                                triggers.</note></pd>
                    </plentry>
                    <plentry>
                        <pt>--inserts</pt>
                        <pd>Dump data as <codeph>INSERT</codeph> commands (rather than
                                <codeph>COPY</codeph>). This will make restoration very slow; it is
                            mainly useful for making dumps that can be loaded into
                            non-PostgreSQL-based databases. Also, since this option generates a
                            separate command for each row, an error in reloading a row causes only
                            that row to be lost rather than the entire table contents. Note that the
                            restore may fail altogether if you have rearranged column order. The
                                <codeph>--column-inserts</codeph> option is safe against column
                            order changes, though even slower. </pd>
                    </plentry>
                    <plentry>
                        <pt>--lock-wait-timeout=<varname>timeout</varname></pt>
                        <pd>Do not wait forever to acquire shared table locks at the beginning of
                            the dump. Instead, fail if unable to lock a table within the specified
                                <varname>timeout</varname>. The timeout may be specified in any of
                            the formats accepted by <codeph>SET statement_timeout</codeph>. Allowed
                            values vary depending on the server version you are dumping from, but an
                            integer number of milliseconds is accepted by all Greenplum Database
                            versions.</pd>
                    </plentry>
                    <plentry>
                        <pt>--no-security-labels</pt>
                        <pd>Do not dump security labels.</pd>
                    </plentry>
                    <plentry>
                        <pt>--no-tablespaces</pt>
                        <pd>Do not output commands to select tablespaces. With this option, all
                            objects will be created in whichever tablespace is the default during
                            restore.</pd>
                    </plentry>
                    <plentry>
                        <pt>--no-unlogged-table-data</pt>
                        <pd>Do not dump the contents of unlogged tables. This option has no
                          effect on whether or not the table definitions (schema) are dumped;
                           it only suppresses dumping the table data.</pd>
                    </plentry>
                    <plentry>
                        <pt>--quote-all-identifiers</pt>
                        <pd>Force quoting of all identifiers. This option is recommended when
                          dumping a database from a server whose Greenplum Database major
                          version is different from <codeph>pg_dumpall</codeph>'s, or when
                          the output is intended to be loaded into a server of a different
                           major version. By default, <codeph>pg_dumpall</codeph> quotes only
                           identifiers that are reserved words in its own major version. This
                           sometimes results in compatibility issues when dealing with servers
                           of other versions that may have slightly different sets of reserved
                           words. Using <codeph>--quote-all-identifiers</codeph> prevents such
                           issues, at the price of a harder-to-read dump script.</pd>
                    </plentry>
                    <plentry>
                        <pt>--resource-queues</pt>
                        <pd>Dump resource queue definitions.</pd>
                    </plentry>
                    <plentry>
                        <pt>--resource-groups</pt>
                        <pd>Dump resource group definitions.</pd>
                    </plentry>
                    <plentry>
                        <pt>--use-set-session-authorization</pt>
                        <pd>Output SQL-standard <codeph>SET SESSION AUTHORIZATION</codeph> commands
                            instead of <codeph>ALTER OWNER</codeph> commands to determine object
                            ownership. This makes the dump more standards compatible, but depending
                            on the history of the objects in the dump, may not restore properly. A
                            dump using <codeph>SET SESSION AUTHORIZATION</codeph> will require
                            superuser privileges to restore correctly, whereas <codeph>ALTER
                                OWNER</codeph> requires lesser privileges.</pd>
                    </plentry>
                    <plentry>
                        <pt>--gp-syntax</pt>
                        <pd>Output Greenplum Database syntax in the <codeph>CREATE
                                TABLE</codeph> statements. This allows the distribution policy
                                (<codeph>DISTRIBUTED BY</codeph> or <codeph>DISTRIBUTED
                                RANDOMLY</codeph> clauses) of a Greenplum Database
                            table to be dumped, which is useful for restoring into other Greenplum Database systems.</pd>
                    </plentry>
                    <plentry>
                        <pt>--no-gp-syntax</pt>
                        <pd>Do not output the table distribution clauses in the <codeph>CREATE
                                TABLE</codeph> statements. </pd>
                    </plentry>
                    <plentry>
                      <pt>-? | --help</pt>
                      <pd>Show help about <codeph>pg_dumpall</codeph> command line arguments, and exit.</pd>
                    </plentry>
                </parml>
            </sectiondiv>
            <sectiondiv id="section6">
                <b>Connection Options</b>
                <parml>
                    <plentry>
                        <pt>-d <varname>connstr</varname> | --dbname=<varname>connstr</varname></pt>
                        <pd>Specifies parameters used to connect to the server, as a connection
                            string. See <xref
                                href="https://www.postgresql.org/docs/9.4/libpq-connect.html#LIBPQ-CONNSTRING"
                                format="html" scope="external">Connection Strings</xref> in the
                            PostgreSQL documentation for more information.</pd>
                        <pd>The option is called <codeph>--dbname</codeph> for consistency with
                            other client applications, but because <codeph>pg_dumpall</codeph> needs
                            to connect to many databases, the database name in the connection string
                            will be ignored. Use the <codeph>-l</codeph> option to specify the name
                            of the database used to dump global objects and to discover what other
                            databases should be dumped.</pd>
                    </plentry>
                    <plentry>
                        <pt>-h <varname>host</varname> | --host=<varname>host</varname></pt>
                        <pd>The host name of the machine on which the Greenplum master database server is running. If
                            not specified, reads from the environment variable
                                <codeph>PGHOST</codeph> or defaults to
                            <codeph>localhost</codeph>.</pd>
                    </plentry>
                    <plentry>
                        <pt>-l <varname>dbname</varname> | --database=<varname>dbname</varname></pt>
                        <pd>Specifies the name of the database in which to connect to dump global objects. If not specified, the <codeph>postgres</codeph> database is used. If the <codeph>postgres</codeph> database does not exist, the <codeph>template1</codeph> database is used.</pd>
                    </plentry>
                    <plentry>
                        <pt>-p <varname>port</varname> | --port=<varname>port</varname></pt>
                        <pd>The TCP port on which the Greenplum master
                            database server is listening for connections. If not specified, reads
                            from the environment variable <codeph>PGPORT</codeph> or defaults to
                            5432.</pd>
                    </plentry>
                    <plentry>
                        <pt>-U <varname>username</varname> | --username=
                            <varname>username</varname></pt>
                        <pd>The database role name to connect as. If not specified, reads from the
                            environment variable <codeph>PGUSER</codeph> or defaults to the current
                            system role name.</pd>
                    </plentry>
                    <plentry>
                        <pt>-w | --no-password</pt>
                        <pd>Never issue a password prompt. If the server requires password
                            authentication and a password is not available by other means such as a
                                <codeph>.pgpass</codeph> file the connection attempt will fail. This
                            option can be useful in batch jobs and scripts where no user is present
                            to enter a password.</pd>
                    </plentry>
                    <plentry>
                        <pt>-W | --password</pt>
                        <pd>Force a password prompt.</pd>
                    </plentry>
                    <plentry>
                        <pt>--role=<varname>rolename</varname></pt>
                        <pd>Specifies a role name to be used to create the dump. This option causes
                                <codeph>pg_dumpall</codeph> to issue a <codeph>SET ROLE
                                &lt;rolename></codeph> command after connecting to the database. It
                            is useful when the authenticated user (specified by <codeph>-U</codeph>)
                            lacks privileges needed by <codeph>pg_dumpall</codeph>, but can switch
                            to a role with the required rights. Some installations have a policy
                            against logging in directly as a superuser, and use of this option
                            allows dumps to be made without violating the policy.</pd>
                    </plentry>
                </parml>
            </sectiondiv>
        </section>
        <section id="section7">
            <title>Notes</title>
            <p>Since <codeph>pg_dumpall</codeph> calls <xref href="pg_dump.xml#topic1" type="topic"
                    format="dita"/> internally, some diagnostic messages will refer to
                    <codeph>pg_dump</codeph>. </p>
            <p>Once restored, it is wise to run <codeph>ANALYZE</codeph> on each database so the
                query planner has useful statistics. You can also run <codeph>vacuumdb -a
                    -z</codeph> to vacuum and analyze all databases. </p>
            <p><codeph>pg_dumpall</codeph> requires all needed tablespace directories to
                exist before the restore; otherwise, database creation will fail for
                databases in non-default locations.</p>
        </section>
        <section id="section8">
            <title>Examples</title>
            <p>To dump all databases: </p>
            <codeblock>pg_dumpall &gt; db.out</codeblock>
            <p>To reload database(s) from this file, you can use:</p>
            <codeblock>psql template1 -f db.out</codeblock>
            <p>To dump only global objects (including resource queues):</p>
            <codeblock>pg_dumpall -g --resource-queues</codeblock>
        </section>
        <section id="section9">
            <title>See Also</title>
            <p>
                <xref href="pg_dump.xml#topic1" type="topic" format="dita"/>
            </p>
        </section>
    </body>
</topic>
